﻿using MySql.Data.MySqlClient;
using System;
using System.Configuration;
using System.Data;

namespace datatablegenerator.DB
{
    public class MySqlDBHelper
    {
        private static MySqlConnection conn = null;

        public MySqlDBHelper()
        {

        }

        public MySqlDBHelper(string connStr)
        {
            using var conn = new MySqlConnection(connStr);

        }

        public MySqlConnection Open()
        {
            return GetConn();
        }

        /// <summary>
        /// 数据库连接
        /// </summary>
        /// <returns></returns>
        private MySqlConnection GetConn()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }

        /// <summary>
        /// 关闭连接
        /// </summary>      
        public static void Close()
        {
            if (conn.State == ConnectionState.Open)
            {
                try
                {
                    conn.Close();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        /// <summary>
        /// 执行查询，返回 DataTable（WPF 绑定友好）
        /// </summary>
        public static DataTable Query(string sql, params MySqlParameter[] ps)
        {
            string connStr = ConfigurationManager.AppSettings["MySQL"];
            using var conn = new MySqlConnection(connStr);
            using var cmd = new MySqlCommand(sql, conn);
            if (ps != null) cmd.Parameters.AddRange(ps);

            var dt = new DataTable();
            conn.Open();
            using var reader = cmd.ExecuteReader();
            dt.Load(reader);
            return dt;
        }

        /// <summary>
        /// 执行 INSERT/UPDATE/DELETE，返回受影响行数
        /// </summary>
        public static int Execute(string sql, params MySqlParameter[] ps)
        {
            string connStr = ConfigurationManager.AppSettings["MySQL"];
            using var conn = new MySqlConnection(connStr);
            using var cmd = new MySqlCommand(sql, conn);
            if (ps != null) cmd.Parameters.AddRange(ps);

            conn.Open();
            return cmd.ExecuteNonQuery();
        }
    }
}
